﻿#include "sql_mgr.h"

#include <QCoreApplication>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QDateTime>
sql_mgr* sql_mgr::instance = nullptr;
sql_mgr::sql_mgr() {}

sql_mgr::~sql_mgr()
{

}

void sql_mgr::init()
{
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    QString strPath = QCoreApplication::applicationDirPath()+"/db/Bookmng.db";
    m_db.setDatabaseName(strPath);

    qDebug()<<m_db.open();
}

bool sql_mgr::login(QString strUser)
{
    QSqlQuery q(m_db);
    QString strSql = QString("select * from User_Info where User_Id='%1'").arg(strUser);//仅库中已经有的用户可以登录
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }else
    {
        ret =q.next();


    }
    return ret;
}

QVector<QStringList> sql_mgr::getUsers(QString strCondition)//获取用户
{
    QSqlQuery q(m_db);
    QString strSql = QString("select * from User_Info %1").arg(strCondition);

    QVector<QStringList> vec;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    else
    {
        int iCols = q.record().count();
        QStringList l;
        while(q.next())
        {
            l.clear();
            for(int i =0;i<iCols;i++)
            {
                l<<q.value(i).toString();
            }
            vec.push_back(l);
        }
    }

    return vec;
}


void sql_mgr::AddUser(QVector<QStringList> v)
{
    m_db.transaction();
    QSqlQuery q(m_db);
    for(auto it:v)
    {
        QString strSql = QString("insert into User_Info VALUES('%1','%2');")
                             .arg(it[0])
                             .arg(it[1]);

        bool ret = q.exec(strSql);
        if(!ret)
        {
            qDebug()<< q.lastError().text();
        }
    }

    m_db.commit();
}

void sql_mgr::delUser(QString strId)
{
    QSqlQuery q(m_db);
    QString strSql = QString("delete from User_Info where User_Id =%1").arg(strId);
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
}

QVector<QStringList> sql_mgr::getBooks(QString strCondition)
{
    QSqlQuery q(m_db);
    QString strSql = QString("select * from BookInfo %1").arg(strCondition);

    QVector<QStringList> vec;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    else
    {
        int iCols = q.record().count();
        QStringList l;
        while(q.next())
        {
            l.clear();
            for(int i =0;i<iCols;i++)
            {
                l<<q.value(i).toString();
            }
            vec.push_back(l);
        }
    }

    return vec;

}

void sql_mgr::AddBooks(QVector<QStringList> vec)
{
    if(0 == vec.size())
    {
        return;
    }
    auto ldata = vec[0];
    QSqlQuery q(m_db);
    QString strSql = QString("INSERT INTO BookInfo "
                             "values('%1','%2','%3','%4','%5')")
                         .arg(ldata[0])
                         .arg(ldata[1])
                         .arg(ldata[2])
                         .arg(ldata[3])
                         .arg(ldata[4]);

    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
}

QString sql_mgr::returnbook(QString strUserId, QString strBookId)
{
    QSqlQuery q(m_db);

    QString strSql = QString("update  BookInfo set available_num = available_num+1 where ISBN=%1").arg(strBookId);

    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    strSql = QString( "delete  from Record where ISBN =%1 and Borrower_Id=%2").arg(strBookId).arg(strUserId);
    ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    return QString("");
}

QString sql_mgr::borrowbookbook(QString strUserId, QString strBookId)
{
    //实现图书借阅

    QSqlQuery q(m_db);

    QString strSql = QString("update  BookInfo set available_num = available_num -1 where ISBN=%1").arg(strBookId);

    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    QDateTime dt = QDateTime::currentDateTime();


    QString str =  dt.toString(Qt::TextDate);

    //auto created = QDateTime::currentSecsSinceEpoch()+3600*24*10;			//创建一个时间对象
   // ui->createtimeE->setText(created.toString("yyyy-MM-dd hh:mm:ss"));
    strSql = QString( "insert into Record VALUES(%1,%2,%3)")
                 .arg(strBookId)
                 .arg(strUserId)
                 .arg(str);
    ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    return QString("");

}

QVector<QStringList> sql_mgr::getRecord(QString strCondition)
{
    //join User_Info using(User_Id) join Book_Info using(ISBN)
    QSqlQuery q(m_db);
    QString strSql = QString("select * from Record   %1").arg(strCondition);

    QVector<QStringList> vec;
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
    else
    {
        int iCols = q.record().count();
        QStringList l;
        while(q.next())
        {
            l.clear();
            for(int i =0;i<iCols;i++)
            {
                l<<q.value(i).toString();
            }
            vec.push_back(l);
        }
    }

    return vec;
}

void sql_mgr::clearRecord()
{
    QSqlQuery q(m_db);
    QString strSql = QString("DELETE from Book_Info ;DELETE FROM sqlite_sequence WHERE name = 'book_name';");
    bool ret = q.exec(strSql);
    if(!ret)
    {
        qDebug()<< q.lastError().text();
    }
}
